﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using cebao.DBUtility;

namespace cebao.DAL
{
    public class dbstatus
    {
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int YBQGetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM VYbq ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        public int YBQGetRecordCountAVG(string strWhere,string type)
        {
            StringBuilder strSql = new StringBuilder();
            if (type == "d")
            {
                strSql.Append("select count(*) from ( SELECT devID FROM (select * FROM VYbq ");
                strSql.Append(" where " + strWhere);
                strSql.Append(") a group by devID,convert(varchar(10),[updateTime],120)) mt ");
            }
            else if (type == "m")
            {
                strSql.Append("select count(*) from ( SELECT devID FROM (select * FROM VYbq ");
                strSql.Append(" where " + strWhere);
                strSql.Append(") a group by devID,convert(varchar(7),[updateTime],120)) mt ");

            }
            else if (type == "x")
            {
                strSql.Append("select count(*) from (SELECT  convert(varchar(7),[updateTime],120)+'-01' as updateTime,devID FROM (select * from VYbq where day(updateTime)<=10");
                strSql.Append(" and " + strWhere);
                strSql.Append(") y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(" union SELECT  convert(varchar(7),[updateTime],120)+'-11' as updateTime,devID FROM (select * from VYbq where day(updateTime)>10 and day(updateTime)<=20");
                strSql.Append(" and " + strWhere);
                strSql.Append(") y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(" union SELECT  convert(varchar(7),[updateTime],120)+'-21' as updateTime,devID FROM (select * from VYbq where day(updateTime)>20");
                strSql.Append(" and " + strWhere);
                strSql.Append(" ) y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(") mt ");
            }
            strSql.Append("select count(1) FROM VYbq ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public int CBJGetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Vcbj ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        public void JBTSQ(string strWhere, string date, out decimal avgwd, out decimal sumyl)
        {
            avgwd = 0;
            sumyl = 0;
            DataSet ds = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [DevID],[XQHID]");
            strSql.Append(" FROM dbo.devinfo WHERE DevType = 'XQH'");
            if (strWhere != "")
                strSql.Append(" and " + strWhere);

            ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
            {

                decimal one = 0;
                string sql = "";
                string did = "";
                string asql = "";
                int wdnum = 0;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[0]["XQHID"].ToString().Contains("空气温度"))
                    {
                        one = 0;
                        sql = "select round(avg(cast([空气温度] as float)),2) as wd from [PGData].[dbo].";

                        did = ds.Tables[0].Rows[i]["DevID"].ToString();
                        asql = sql + "[" + did + "] ";
                        if (date != "")
                            asql += "where " + date;
                        decimal.TryParse(DbHelperSQL.GetSingle(asql).ToString(), out one);
                        wdnum++;
                        avgwd += one;
                    }
                    if (ds.Tables[0].Rows[0]["XQHID"].ToString().Contains("雨量"))
                    {
                        one = 0;
                        sql = "select round(sum(cast([雨量] as float)),2) as yl from [PGData].[dbo].";

                        did = ds.Tables[0].Rows[i]["DevID"].ToString();
                        asql = sql + "[" + did + "] ";
                        if (date != "")
                            asql += "where " + date;
                        decimal.TryParse(DbHelperSQL.GetSingle(asql).ToString(), out one);
                        sumyl += one;
                    }

                }
                if (wdnum!=0)
                avgwd = avgwd / wdnum;

            }
        }
        public DataSet HZTSQ(string strWhere, string date)
        {
            DataSet ds = null;
            DataSet dstsq = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [KeyId],[DevType],[DevID],[devname],[DevLocation],[DevOwnerName],[DevOwnerID],[DevContact],[XQHID],[chn0],[chn1],[chn2],[chn3],[mjd],[mwd],[mdatetime],[mmark],[mpic]");
            strSql.Append(" FROM         dbo.devinfo WHERE DevType = 'TSQ'");
            if (strWhere != "")
                strSql.Append(" and " + strWhere);

            ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
            {
                //[土壤温度1],[土壤温度2],[土壤温度3],[土壤温度4],[土壤温度5],[土壤湿度1],[土壤湿度2],[土壤湿度3],[土壤湿度4],[土壤湿度5],datetime
                ds.Tables[0].Columns.Add("土壤温度1", typeof(string));
                ds.Tables[0].Columns.Add("土壤温度2", typeof(string));
                ds.Tables[0].Columns.Add("土壤温度3", typeof(string));
                ds.Tables[0].Columns.Add("土壤温度4", typeof(string));
                ds.Tables[0].Columns.Add("土壤温度5", typeof(string));
                ds.Tables[0].Columns.Add("土壤湿度1", typeof(string));
                ds.Tables[0].Columns.Add("土壤湿度2", typeof(string));
                ds.Tables[0].Columns.Add("土壤湿度3", typeof(string));
                ds.Tables[0].Columns.Add("土壤湿度4", typeof(string));
                ds.Tables[0].Columns.Add("土壤湿度5", typeof(string));
                ds.Tables[0].Columns.Add("datetime", typeof(DateTime));
                string did = "";
                string asql = "";
                string sql = "select round(avg(cast([土壤温度1] as float)),2) as 土壤温度1,round(avg(cast([土壤温度2] as float)),2) as 土壤温度2 ,round(avg(cast([土壤温度3] as float)),2) as 土壤温度3,round(avg(cast([土壤温度4] as float)),2) as 土壤温度4,round(avg(cast([土壤温度5] as float)),2) as 土壤温度5,round(avg(cast([土壤湿度1] as float)),2) as 土壤湿度1 ,round(avg(cast([土壤湿度2] as float)),2) as 土壤湿度2 ,round(avg(cast([土壤湿度3] as float)),2) as 土壤湿度3 ,round(avg(cast([土壤湿度4] as float)),2) as 土壤湿度4 ,round(avg(cast([土壤湿度5] as float)),2) as 土壤湿度5 from [SqData].[dbo].";
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    asql = "";
                    did = ds.Tables[0].Rows[i]["DevID"].ToString();
                    asql = sql + "[" + did + "] ";
                    if (date != "")
                        asql += "where " + date;

                    dstsq = DbHelperSQL.Query(asql);
                    if (dstsq != null)
                    {
                        ds.Tables[0].Rows[i]["土壤温度1"] = dstsq.Tables[0].Rows[0]["土壤温度1"].ToString();
                        ds.Tables[0].Rows[i]["土壤温度2"] = dstsq.Tables[0].Rows[0]["土壤温度2"].ToString();
                        ds.Tables[0].Rows[i]["土壤温度3"] = dstsq.Tables[0].Rows[0]["土壤温度3"].ToString();
                        ds.Tables[0].Rows[i]["土壤温度4"] = dstsq.Tables[0].Rows[0]["土壤温度4"].ToString();
                        ds.Tables[0].Rows[i]["土壤温度5"] = dstsq.Tables[0].Rows[0]["土壤温度5"].ToString();
                        ds.Tables[0].Rows[i]["土壤湿度1"] = dstsq.Tables[0].Rows[0]["土壤湿度1"].ToString();
                        ds.Tables[0].Rows[i]["土壤湿度2"] = dstsq.Tables[0].Rows[0]["土壤湿度2"].ToString();
                        ds.Tables[0].Rows[i]["土壤湿度3"] = dstsq.Tables[0].Rows[0]["土壤湿度3"].ToString();
                        ds.Tables[0].Rows[i]["土壤湿度4"] = dstsq.Tables[0].Rows[0]["土壤湿度4"].ToString();
                        ds.Tables[0].Rows[i]["土壤湿度5"] = dstsq.Tables[0].Rows[0]["土壤湿度5"].ToString();
                    }
                }
            }
            return ds;
        }
        public DataSet HZYBQ(string strWhere, string date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT     dbo.devinfo.DevID, dbo.devinfo.devname, dbo.devinfo.DevLocation, dbo.devinfo.DevOwnerID, t.ch0, t.ch1, t.ch2, t.ch3, dbo.devinfo.chn0, dbo.devinfo.chn1, dbo.devinfo.chn2, dbo.devinfo.chn3");
            strSql.Append(" FROM         dbo.devinfo INNER JOIN");
            strSql.Append(" (SELECT     devID, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3");
            strSql.Append(" FROM          dbo.VYbq");
            if (date != "")
                strSql.Append(" WHERE   " + date);
            strSql.Append(" GROUP BY devID) AS t ON dbo.devinfo.DevID = t.devID");
            strSql.Append(" WHERE     (dbo.devinfo.DevType = 'YBQ')");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet HZCBJ(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.updateTime desc");
            }
            strSql.Append(")AS Row, T.*  from VcbjHZ T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet YBQGetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.updateTime desc");
            }
            strSql.Append(")AS Row, T.*  from VYbq T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet YBQGetListByPageAVG(string strWhere, string orderby, int startIndex, int endIndex,string type)
        {
            StringBuilder strSql = new StringBuilder();
            if (type == "d")
            {
                strSql.Append("SELECT * FROM ( ");
                strSql.Append(" SELECT ROW_NUMBER() OVER (");
                strSql.Append("order by T.updateTime desc");
                strSql.Append(")AS Row, T.*  from (SELECT devID, CONVERT(varchar(10), updateTime, 120) AS updateTime, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3 from (select * from VYbq");
                strSql.Append(" WHERE " + strWhere);
                strSql.Append(") mt group by devID,convert(varchar(10),[updateTime],120))  T ) TT");
                strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            }
            else if (type == "m")
            {
                strSql.Append("SELECT * FROM ( ");
                strSql.Append(" SELECT ROW_NUMBER() OVER (");
                strSql.Append("order by T.updateTime desc");
                strSql.Append(")AS Row, T.*  from (SELECT devID, CONVERT(varchar(7), updateTime, 120)+'-1' AS updateTime, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3 from (select * from VYbq");
                strSql.Append(" WHERE " + strWhere);
                strSql.Append(") mt group by devID,convert(varchar(7),[updateTime],120))  T ) TT");
                strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);

            }
            else if (type == "x")
            {
                strSql.Append("SELECT * FROM ( ");
                strSql.Append(" SELECT ROW_NUMBER() OVER (");
                strSql.Append("order by T.updateTime desc");
                strSql.Append(")AS Row, T.*  from (SELECT devID, CONVERT(varchar(7), updateTime, 120)+'-01' AS updateTime, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3 from (select * from VYbq where day(updateTime)<=10");
                strSql.Append(" and " + strWhere);
                strSql.Append(") y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(" union SELECT devID, CONVERT(varchar(7), updateTime, 120)+'-11' AS updateTime, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3 from (select * from VYbq where day(updateTime) >10 and day(updateTime)<=20");
                strSql.Append(" and " + strWhere);
                strSql.Append(") y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(" union SELECT devID, CONVERT(varchar(7), updateTime, 120)+'-21' AS updateTime, SUM(ch0) AS ch0, SUM(ch1) AS ch1, SUM(ch2) AS ch2, SUM(ch3) AS ch3 from (select * from VYbq where day(updateTime)>20");
                strSql.Append(" and " + strWhere);
                strSql.Append(") y group by devID,convert(varchar(7),[updateTime],120) ");
                strSql.Append(") T ) TT");
                strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);

            }            
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet CBJGetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.updateTime desc");
            }
            strSql.Append(")AS Row, T.*  from Vcbj T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetTSQ(string devid, int PageSize, int PageIndex, string strWhere, out int count)
        {
            //int count=0;
            SqlParameter[] parameters = {
            new SqlParameter("@TableName", SqlDbType.VarChar, 255),
            new SqlParameter("@KeyFields", SqlDbType.VarChar, 255),
            new SqlParameter("@PageSize", SqlDbType.Int),
            new SqlParameter("@PageIndex", SqlDbType.Int),
            new SqlParameter("@OrderFields", SqlDbType.VarChar,255),            
            new SqlParameter("@WhereString", SqlDbType.VarChar,1000),
            new SqlParameter("@RecordCount", SqlDbType.Int)
            };
            parameters[0].Value = devid;
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = "datetime desc";
            parameters[5].Value = strWhere;
            parameters[6].Direction = ParameterDirection.Output;

            DataSet ds = DbHelperSQL.RunProcedure("ProcCustomPageTSQ", parameters, "ds");
            int c = 0;
            int.TryParse(parameters[6].Value.ToString(), out c);
            count = c;
            return ds;
        }
        public DataSet GetXQH(string devid, int PageSize, int PageIndex, string strWhere, out int count, string type)
        {
            //int count=0;
            SqlParameter[] parameters = {
            new SqlParameter("@TableName", SqlDbType.VarChar, 255),
            new SqlParameter("@KeyFields", SqlDbType.VarChar, 255),
            new SqlParameter("@PageSize", SqlDbType.Int),
            new SqlParameter("@PageIndex", SqlDbType.Int),
            new SqlParameter("@OrderFields", SqlDbType.VarChar,255),            
            new SqlParameter("@WhereString", SqlDbType.VarChar,1000),
            new SqlParameter("@RecordCount", SqlDbType.Int)
            };
            parameters[0].Value = devid;
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            if(type=="")
            parameters[4].Value = "m.datetime desc";
            else
                parameters[4].Value = "datetime desc";
            parameters[5].Value = strWhere;
            parameters[6].Direction = ParameterDirection.Output;
            DataSet ds = null;
            try
            {
                if (type == "")
                    ds = DbHelperSQL.RunProcedure("ProcCustomPage", parameters, "ds");
                else if (type == "A")
                    ds = DbHelperSQL.RunProcedure("ProcCustomPageAvg", parameters, "ds");
                else if (type == "M")
                    ds = DbHelperSQL.RunProcedure("ProcCustomPageMAvg", parameters, "ds");
            }
            catch (Exception ex)
            {

                string a = ex.Message;
            }
            int c = 0;
            int.TryParse(parameters[6].Value.ToString(), out c);
            count = c;
            return ds;
        }
        public string GetPhnum(string xqhid)
        {
            string sql = "select TD from VPhnum where deviceid=@deviceid";
            SqlParameter[] parameters = {
					new SqlParameter("@deviceid", SqlDbType.VarChar,400)
			};
            parameters[0].Value = xqhid;
            object o = DbHelperSQL.GetSingle(sql, parameters);
            if (o== null)
                return "";
            else
                return o.ToString();
        }

    }
}
